
package com.csz.model;

import com.csz.dao.Persistencia;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


/* @author Arthur F. Zanona */

public class Caracteristica extends Persistencia {
    private static final long serialVersionUID = 1L;
    private String nome;

    public Caracteristica() {
    }

    public Caracteristica(int id, String nome ) {
        this.id = id;
        this.nome = nome;
    }
   
    public String getNome() {
        return nome;
    }

    public void setNome(String nome) {
        this.nome = nome;
    }

    public static List<Object> consultar(){
        return Caracteristica.consultarNamedQuery(ConsultarTodos, Arrays.asList(new Object[]{}));
    }

   public static List<Object> consultarNamedQuery(String namedQuery, List params){
        PreparedStatement ps = createStatementNamedQuery( namedQuery, params );
        List<Object> lista = new ArrayList<Object>();
        try {
            ResultSet rs = ps.executeQuery();
            while ( rs.next() ) {
                Caracteristica c = new Caracteristica( rs.getInt("car_id")  , rs.getString("car_nome") );
                lista.add(c);
            }
        }
        catch (SQLException ex) {}
        return lista;
    }

    @Override
    public void salvar() {
        String namedQuery = this.getId() == null ? Inserir : Atualizar ;
        //----- params aqui
        List<Object> params = new ArrayList<Object>();
        //---- fim params
        if ( this.getId() != null ) params.add( this.getId() );
        PreparedStatement ps = createStatementNamedQuery(namedQuery, params);
        try {
            ps.execute();
        } catch (SQLException ex) {}
    }
    
    public static Object consultar(Integer id) {
        List<Object> l = Caracteristica.consultarNamedQuery(ConsultarPorId, Arrays.asList(new Object[]{id}));
        return l == null ? null : l.get(0);
    }

    @Override
    public void remover() {
        PreparedStatement ps = createStatementNamedQuery(Remover, Arrays.asList( new Object[] { this.getId() } ));
        try {
            ps.execute();
        } catch (SQLException ex) {}
    }

    // QUERY SQL --------------------------------------------------------------------------------------------------------------
    public static final String ConsultarPorBioId = "select car_id,car_nome from caracteristica where car_id not in ( " +
                                                    " select sco_car_id from score s where sco_car_id = car_id and sco_bio_id = ? ) ";

    public static final String ConsultarTodos = "select car_id,car_nome from caracteristica";

    public static final String ConsultarPorCategoriaBiotipo = "SELECT car_id , car_nome FROM categoria_caracteristica cac " +
                                                       " JOIN caracteristica car ON car.car_id = cac.cac_car_id " +
                                                       " WHERE ( ( cac_cat_id = ? ) and ( cac_car_id not in (select sco_car_id from score s where sco_car_id = car_id and sco_bio_id = ? )) )";

    public static final String ConsultarPorSubCategoriaBiotipo = "SELECT car_id , car_nome FROM categoria_caracteristica cac " +
                                                       " JOIN caracteristica car ON car.car_id = cac.cac_car_id " +
                                                       " WHERE ( ( cac_cat_id = ? ) and ( cac_car_id not in (select sco_car_id from score s where sco_car_id = car_id and sco_bio_id = ? )) )";

    public static final String ConsultarPorId = "SELECT .. FROM .. WHERE .._id = ?";
    private static final String Remover = "DELETE FROM .. WHERE .._id = ?";
    private static final String Atualizar = "UPDATE .. SET .. ";
    private static final String Inserir = "INSERT INTO .. (..) VALUES (?,?,..)";
    // FIM QUERY SQL --------------------------------------------------------------------------------------------------------------
    
}
